﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Text;

namespace Service.DB
{
    public class SQLiteHelper
    {
        #region 成员
        private static SQLiteHelper _Instance;
        /// <summary>
        /// 获取实例
        /// </summary>
        /// <returns>DbService.</returns>
        public static SQLiteHelper Instance
        {
            get
            {
                // 当第一个线程运行到这里时，此时会对locker对象 "加锁"，
                // 当第二个线程运行该方法时，首先检测到locker对象为"加锁"状态，该线程就会挂起等待第一个线程解锁
                // lock语句运行完之后（即线程运行完之后）会对该对象"解锁"
                lock (Locker)
                {
                    // 如果类的实例不存在则创建，否则直接返回
                    if (_Instance == null)
                    {
                        _Instance = new SQLiteHelper();
                    }
                }

                return _Instance;
            }
        }
        #endregion

        #region 属性
        /// <summary>
        /// 定义一个标识确保线程同步
        /// </summary>
        private static readonly object Locker = new object();

        /// <summary>
        /// 本地数据库
        /// </summary>
        static string configFolder = Path.Combine(Environment.CurrentDirectory, "DB");
        #endregion

        #region 方法

        #region 连接
        /// <summary>
        /// Gets the sqlite connection.
        /// </summary>
        /// <param name="open">if set to <c>true</c> [open].</param>
        /// <returns>SQLiteConnection.</returns>
        /// <exception cref="Exception"></exception>
        protected SQLiteConnection GetSqliteConnection(bool open = true)
        {
            try
            {
                string fullPath = Path.Combine(configFolder, "dbName");
                string connStr = string.Format("Data Source={0};Version=3;", fullPath);

                var connection = new SQLiteConnection(connStr);

                if (open)
                    connection.Open();
                return connection;
            }
            catch (Exception ex)
            {

            }
            throw new Exception();
        }

        #endregion

        #region 获取数据
        /// <summary>
        /// 分页数据
        /// </summary>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageCount">页数量</param>
        /// <param name="searchFilter">The search filter.</param>
        /// <param name="id">The clinic identifier.</param>
        public List<string> GetDatas(int pageIndex, int pageCount, string searchFilter, string id)
        {
            List<string> clinicPatients = new List<string>();
            try
            {
                using (var connection = GetSqliteConnection())
                {
                    int startRow = pageIndex * pageCount < 0 ? 0 : pageIndex * pageCount;
                    if (string.IsNullOrWhiteSpace(searchFilter))
                    {
                        clinicPatients = connection.Query<string>(@"select * from xxx where id = '" + id + "' limit " +
                            pageCount + " offset " + startRow).ToList();
                    }
                    else
                    {
                        string filter = "where id = '" + id + "'";
                        filter += " and (name like '%" + searchFilter + "%')";
                        clinicPatients = connection.Query<string>(@"select * from xxx " + filter + " limit " + pageCount + " offset " + startRow).ToList();
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return clinicPatients;
        } 
        #endregion

        #region 导入数据

        /// <summary>
        /// 批量导入患者
        /// </summary>
        /// <param name="commonDatas">患者集合</param>
        public void ReplaceDatas(List<string> commonDatas)
        {
            try
            {
                if (commonDatas == null || commonDatas.Count == 0)
                {
                    return;
                }
                using (var connection = GetSqliteConnection())
                {
                    using (var trans = connection.BeginTransaction())
                    {
                        connection.Execute(@"replace into ClinicPatient(id, userNamee) values(@id,  @userName)", commonDatas, trans);
                        trans.Commit();
                    }
                }
            }
            catch (Exception ex)
            {
            }
        }
        #endregion

        #endregion
    }
}
